/**
 * Copyright 2011 HNB. All rights reserved.
 * HNB PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */
package my.hnb.android.model;

import java.util.ArrayList;

import my.hnb.android.common.AppInfo;
import my.hnb.android.constant.Constants;
import my.hnb.android.dto.OptionItemDTO;
import my.hnb.android.dto.SongDTO;
import my.hnb.android.dto.SongDetailDTO;
import my.hnb.android.ultils.StringUtil;
import my.hnb.android.ultils.download.ExternalStorage;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/**
 * Cac ham truy xuat contact trong SQLLite
 * 
 * @author: BangHN
 * @version: 1.0
 * @since: 1.0
 */
public class SQLUtils {
	// private static DatabaseHelper mDbHelper;
	private static SQLiteDatabase mDB;
	private static SQLUtils instance = null;
	public boolean isProcessingTrans = false;

	private static final Object lockObject = new Object();

	public static SQLUtils getInstance() {
		if (instance == null) {
			synchronized (lockObject) {
				if (instance == null) {
					instance = new SQLUtils();
				}
			}
		}
		if (mDB == null || !mDB.isOpen()) {
			synchronized (lockObject) {
				if (mDB == null || !mDB.isOpen()) {
					try {
						mDB = SQLiteDatabase.openDatabase(
								ExternalStorage.getFileDBPath() + "/"
										+ Constants.DATABASE_NAME, null,
								SQLiteDatabase.OPEN_READWRITE);
					} catch (Throwable e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		}
		return instance;
	}

	public SQLiteDatabase getmDB() {
		return mDB;
	}

	/**
	 * Thuc hien 1 cau lenh sql tong quat
	 * 
	 * @author: BangHN
	 * @param sqlQuery
	 * @param params
	 * @return
	 * @return: Cursor
	 * @throws:
	 */
	public Cursor rawQuery(String sqlQuery, String[] params) {
		Cursor cursor = null;
		if (mDB != null && mDB.isOpen()) {
			cursor = mDB.rawQuery(sqlQuery, params);
		}
		return cursor;
	}

	public Cursor rawQueries(String sqlQuery, ArrayList<String> params) {
		String[] strParams = null;
		if (params != null) {
			strParams = new String[params.size()];
			for (int i = 0, s = params.size(); i < s; i++) {
				strParams[i] = params.get(i);
			}
		}
		return mDB.rawQuery(sqlQuery, strParams);
	}

	/**
	 * Close DB
	 * 
	 * @author: BangHN
	 * @return: void
	 * @throws:
	 */
	public void closeDB() {
		try {
			if (mDB != null) {
				if (mDB.inTransaction()) {
					mDB.endTransaction();
				}
				if (mDB.isOpen()) {
					mDB.close();
				}
				mDB = null;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	/**
	 * ------------------------------------------------------------------------
	 * ------------- CAI DAT CAC FUNCTION PHUC VU VIEC LAY DU LIEU TU NGHIEP VU
	 * -- ----------------------------------------------------------------------
	 * -- -----------
	 */

	public boolean isExistSong(String songCode) {
		boolean isExist = false;
		Cursor c = null;
		try {
			// tat ca cac bang deu co truong: SYN_STATE
			String query = "Select SONG_CODE from SONG  where SONG_CODE = "
					+ songCode;
			c = rawQuery(query, null);
			if (c.getCount() <= 0) {
				isExist = false;
			} else {
				isExist = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (Exception ex) {
				}
			}
		}
		return isExist;
	}

	/**
	 * Lay thong tin customer trong : CUSTOMER & CUSTOMER_TYPE
	 * 
	 * @author banghn
	 */
	public ArrayList<SongDTO> getSongs(String searchInfo, int page,
			int searchOption, int favorite) {
		ArrayList<SongDTO> listSong = new ArrayList<SongDTO>();
		String paramVolOption = "";
		String paramTypeOption = "";
		ArrayList<String> optionVol = new ArrayList<String>();
		ArrayList<String> optionType = new ArrayList<String>();
		if (searchOption == 1) {
			if (!AppInfo.getInstance().getMusicVolOption().get(0).isSelected) {
				for (int i = 1; i < AppInfo.getInstance().getMusicVolOption()
						.size(); i++) {
					if (AppInfo.getInstance().getMusicVolOption().get(i)
							.isSelected()) {
						optionVol.add(""
								+ AppInfo.getInstance().getMusicVolOption()
										.get(i).getValue());
					}
				}
				for (int i = 0; i < optionVol.size() - 1; i++) {
					paramVolOption += optionVol.get(i) + ",";
				}
				paramVolOption += optionVol.get(optionVol.size() - 1);
			}

			if (!AppInfo.getInstance().getMusicTypeOption().get(0).isSelected) {
				for (int i = 1; i < AppInfo.getInstance().getMusicTypeOption()
						.size(); i++) {
					if (AppInfo.getInstance().getMusicTypeOption().get(i)
							.isSelected()) {
						optionType.add(""
								+ AppInfo.getInstance().getMusicTypeOption()
										.get(i).getValue());
					}
				}
				if (optionType.size() == 1) {
					paramTypeOption = "	and  TYPE like '%" + optionType.get(0)
							+ "%' ";
				} else if (optionType.size() > 1) {
					paramTypeOption = "	and  (TYPE like '%" + optionType.get(0)
							+ "%' ";
					for (int i = 1; i < optionType.size(); i++) {
						paramTypeOption += " or TYPE like '%"
								+ optionType.get(i) + "%' ";
					}
					paramTypeOption += " )";
				}

			}

		}

		ArrayList<String> param = new ArrayList<String>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SONG_CODE as SONG_NUMBER, ");
		sql.append("       SONG_NAME as SONG_NAME, ");
		sql.append("       FAVORITE as FAVORITE, ");
		sql.append("       LYRIC_SHORT as SHORT_LYRIC ");
		sql.append("FROM   song ");
		sql.append("WHERE  1 = 1 ");
		// sql.append("	and  LANGUAGE = '1' ");
		if (!StringUtil.isNullOrEmpty(searchInfo)) {
			sql.append("	and  (SONG_NAME_SEARCH like ?");
			param.add("%"
					+ StringUtil.getEngStringFromUnicodeString(searchInfo)
							.toUpperCase() + "%");
			sql.append("	or  SONG_NAME_SHORT like ?");
			param.add("%"
					+ StringUtil.getEngStringFromUnicodeString(searchInfo)
							.toUpperCase() + "%");

			// song-code
			sql.append("	or  SONG_CODE like ?");
			param.add("%"
					+ StringUtil.getEngStringFromUnicodeString(searchInfo)
							.toUpperCase() + "%");
			// MUSICIAN
			if (AppInfo.getInstance().getMusicSearchOption().get(2).isSelected) {
				sql.append("	or  MUSICAN_SEARCH like ?");
				param.add("%"
						+ StringUtil.getEngStringFromUnicodeString(searchInfo)
								.toUpperCase() + "%");
			}
			// LYRIC
			if (AppInfo.getInstance().getMusicSearchOption().get(3).isSelected) {
				sql.append("	or  LYRIC_SEARCH like ?");
				param.add("%"
						+ StringUtil.getEngStringFromUnicodeString(searchInfo)
								.toUpperCase() + "%");
			}
			sql.append("	)");
		}

		if (favorite == 1) {
			sql.append("	and  FAVORITE = 1 ");
		}
		if (searchOption == 1) {
			if (!StringUtil.isNullOrEmpty(paramVolOption)) {
				sql.append("	and  VOL in (" + paramVolOption + ")");
			}
			if (!StringUtil.isNullOrEmpty(paramTypeOption)) {
				sql.append(paramTypeOption);
			}
		}

		sql.append(" ORDER  BY (CASE WHEN SONG_NAME_SEARCH LIKE ? THEN 0 ");
		param.add(StringUtil.getEngStringFromUnicodeString(searchInfo).toUpperCase() + "%");
		sql.append(" 	WHEN SONG_NAME_SEARCH LIKE ? THEN 1  ELSE 2 END),");
		param.add("%" + StringUtil.getEngStringFromUnicodeString(searchInfo).toUpperCase() + "%");

		sql.append(" SONG_NAME_SEARCH ");
		
		// sql.append("LIMIT  20 offset 20 ");
		sql.append(" limit " + Integer.toString(Constants.NUM_ITEM_PER_PAGE));
		sql.append(" offset "
				+ Integer.toString((page - 1) * Constants.NUM_ITEM_PER_PAGE));
		Cursor c = null;
		try {
			c = rawQueries(sql.toString(), param);
			if (c != null) {
				SongDTO dto;
				if (c.moveToFirst()) {
					do {
						dto = new SongDTO();
						dto.parseSongInfo(c);
						listSong.add(dto);
					} while (c.moveToNext());
				}
			}
		} catch (Exception e) {
			return null;
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (Exception ex) {
				}
			}
		}
		return listSong;
	}

	/**
	 * lay thong tin bai hat chi tiet
	 * 
	 * @author BANGHN
	 * @param songCode
	 * @return
	 */
	public SongDetailDTO getSongDetail(String songCode) {
		SongDetailDTO song = new SongDetailDTO();

		ArrayList<String> param = new ArrayList<String>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SONG_CODE    AS SONG_NUMBER, ");
		sql.append("       SONG_NAME    AS SONG_NAME, ");
		sql.append("       VOL      	AS VOL, ");
		sql.append("       MUSICAN      AS MUSICAN, ");
		sql.append("       SINGER       AS SINGER, ");
		sql.append("       LYRIC_FULL   AS FULL_LYRIC, ");
		sql.append("       TYPE		    AS TYPE, ");
		sql.append("       FAVORITE     AS FAVORITE ");
		sql.append("FROM   song ");
		sql.append("WHERE  SONG_CODE = ?");
		param.add(songCode);
		Cursor c = null;
		try {
			c = rawQueries(sql.toString(), param);
			if (c != null) {
				if (c.moveToFirst()) {
					song.parseSongDetailInfo(c);
				}
			}
		} catch (Exception e) {
			return null;
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (Exception ex) {
				}
			}
		}
		return song;
	}

	/**
	 * lay thong tin bai hat chi tiet
	 * 
	 * @author BANGHN
	 * @param songCode
	 * @return
	 */
	public int updateSongFavorite(String songCode, int favorite) {
		ArrayList<String> param = new ArrayList<String>();
		StringBuffer sql = new StringBuffer();
		sql.append("update song set FAVORITE = ? where SONG_CODE = ?");
		param.add("" + favorite);
		param.add(songCode);

		try {
			if (mDB != null) {
				mDB.execSQL(sql.toString(), param.toArray());
			}
		} catch (Exception e) {
			return -1;
		}
		return 1;
	}

	public ArrayList<SongDTO> getTileOfSong() {
		ArrayList<SongDTO> listSong = new ArrayList<SongDTO>();

		ArrayList<String> param = new ArrayList<String>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SONG_CODE as SONG_NUMBER, ");
		sql.append("       SONG_NAME_SEARCH as SONG_NAME ");
		sql.append("FROM   SONG ");
		Cursor c = null;
		try {
			c = rawQueries(sql.toString(), param);
			if (c != null) {
				SongDTO dto;
				if (c.moveToFirst()) {
					do {
						dto = new SongDTO();
						dto.parseSongTitle(c);
						listSong.add(dto);
					} while (c.moveToNext());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (Exception ex) {
				}
			}
		}
		return listSong;
	}

	/**
	 * lay thong tin bai hat chi tiet
	 * 
	 * @author BANGHN
	 * @param songCode
	 * @return
	 */
	public int updateSongTile(long songCode, String songTile) {
		ArrayList<String> param = new ArrayList<String>();
		StringBuffer sql = new StringBuffer();
		sql.append("update song set SONG_NAME_SHORT = ? where SONG_CODE = ?");
		param.add(songTile);
		param.add("" + songCode);

		try {
			if (mDB != null) {
				mDB.execSQL(sql.toString(), param.toArray());
			}
		} catch (Exception e) {
			return -1;
		}
		return 1;
	}

	/**
	 * Lay danh sach vol nhac moi gan day
	 * 
	 * @author BANGHN
	 * @return
	 */
	public ArrayList<OptionItemDTO> getVolNew() {
		OptionItemDTO dto;
		ArrayList<OptionItemDTO> volList = new ArrayList<OptionItemDTO>();
		dto = new OptionItemDTO();
		dto.setTextMenu("Tất cả (Bao gồm Vol cũ)");
		dto.setIsSelected(true);
		volList.add(dto);

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT vol FROM song ");
		sql.append("     order by  CAST(vol AS SIGNED) desc limit 3; ");

		Cursor c = null;
		try {
			c = rawQueries(sql.toString(), null);
			if (c != null) {
				if (c.moveToFirst()) {
					do {
						dto = new OptionItemDTO();
						dto.parseVolSong(c);
						volList.add(dto);
					} while (c.moveToNext());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (Exception ex) {
				}
			}
		}
		return volList;
	}

	/**
	 * Lay danh sach the loai nhac
	 * 
	 * @author BANGHN
	 * @return
	 */
	public ArrayList<OptionItemDTO> getMusicType() {
		OptionItemDTO dto;
		ArrayList<OptionItemDTO> arrayMusicType = new ArrayList<OptionItemDTO>();
		dto = new OptionItemDTO();
		dto.value = -1;
		dto.strText = "Tất cả thể loại";
		dto.isSelected = true;
		arrayMusicType.add(dto);

		StringBuffer sql = new StringBuffer();
		//ko lay the loai lien khuc, nhac cach mang
		sql.append("SELECT * FROM TYPE WHERE ID NOT IN (0,3) ORDER BY ID DESC ");
		Cursor c = null;
		try {
			c = rawQueries(sql.toString(), null);
			if (c != null) {
				if (c.moveToFirst()) {
					do {
						dto = new OptionItemDTO();
						dto.parseMusicType(c);
						arrayMusicType.add(dto);
					} while (c.moveToNext());
				}
				//arrayMusicType.remove(arrayMusicType.size() - 1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (Exception ex) {
				}
			}
		}
		return arrayMusicType;
	}

	public int getNumberOfSong() {
		int numberSong = 0;
		Cursor c = null;
		try {
			c = rawQueries("Select count(1) as NUMBER from SONG", null);
			if (c != null) {
				if (c.moveToFirst()) {
					numberSong = c.getInt(c.getColumnIndex("NUMBER"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (c != null) {
				try {
					c.close();
				} catch (Exception ex) {
				}
			}
		}
		return numberSong;
	}
}
